# -*- encoding: utf-8 -*-
from odoo import models, fields, api, _
from odoo.exceptions import UserError


class account_summary_wizard(models.Model):
    _name = 'account.summary.wizard'
    _description = u'总分类帐查询'
    _rec_name = 'name'

    name = fields.Char(default='总分类帐查询', string='总分类帐查询')
    period_id = fields.Many2one('account.period', u'期别')
    start_date = fields.Date(u'起始日')
    end_date = fields.Date(u'截止日')
    from_account_id = fields.Many2one('cncw.account', u'起始会科')
    to_account_id = fields.Many2one('cncw.account', u'截止会科')
    line_ids = fields.One2many('account.summary', 'master_id', u'明细')

    @api.onchange('period_id')
    def onchange_period_id(self):
        if self.period_id:
            self.start_date = self.period_id.date_start
            self.end_date = self.period_id.date_stop

    @api.onchange('from_account_id')
    def onchange_from_account_id(self):
        if self.from_account_id:
            self.to_account_id = self.from_account_id.id


    def action_confirm(self):
        self.ensure_one()
        self._cr.commit()
        if not self.from_account_id:
            raise UserError(_(u'开始会科不可为空!'))

        if not self.to_account_id:
            raise UserError(_(u'截止会科不可为空!'))
        sql = """
            select account_summary_settle(%s,'%s','%s',%s,%s);
            """ % (self.period_id.id, self.from_account_id.code, self.to_account_id.code, self._uid, self.id)
        self._cr.execute(sql)
        self._cr.commit()

    # 导出


class account_summary(models.Model):
    _name = 'account.summary'
    _description = u'总分类帐查询'

    master_id = fields.Many2one('account.summary.wizard', u'主档', ondelete="cascade")
    sequence = fields.Integer(u'序', default=1)
    account_id = fields.Many2one('cncw.account', u'会科', ondelete="set null")
    name = fields.Char(u'凭证编号')
    date = fields.Date(u'凭证日期')
    line_name = fields.Char(u'摘要')
    begin_amount = fields.Float(u'期初', digits='Amount')
    debit = fields.Float(u'借方金额', digits='Amount')
    credit = fields.Float(u'贷方金额', digits='Amount')
    end_amount = fields.Float(u'期末', digits='Amount')
    sub_account_id = fields.Many2one('res.partner', u'会子科目', ondelete="restrict")
    uid = fields.Many2one('res.users', u'当前用户', ondelete="set null")
    period_id = fields.Many2one('account.period', u'期别', ondelete="set null")

    def init(self):
        self._cr.execute("""
                        --DROP FUNCTION account_summary_settle(integer,character varying,character varying,integer,integer);
                        CREATE OR REPLACE FUNCTION account_summary_settle(
                                period_t integer,
                                from_account_no character varying,
                                to_account_no character varying,
                                uid_t integer,
                                master_id_t integer)
                          RETURNS void AS
                        $BODY$
                        -- 总分类帐查询
                        -- DROP FUNCTION account_summary_settle(integer,character varying,character varying,integer,integer)
                        -- select account_summary_settle(9,'10011','10011',1,1)

                        declare
                                start_date_t timestamp;
                                end_date_t timestamp;
                                account_t integer;
                                begin_amount_t numeric:=0.0;
                                end_t numeric:=0.0;
                                rec record;
                                rec_account record;
                                seq integer:=0;
                        begin

                             delete from account_summary where uid=uid_t and master_id=master_id_t;
                             select id,date_start,date_stop into period_t,start_date_t,end_date_t from account_period where id=period_t;

                             for rec_account in select id  from cncw_account where code between from_account_no and to_account_no and sub_account_type<>'view'
                             loop
                                  --1.期初
                                  select coalesce(begin,0) into  begin_amount_t from account_balance where period_id=period_t and account_id=rec_account.id;
                                      seq:=1;

                                  insert into account_summary(sequence,account_id,line_name,end_amount,uid,period_id,create_uid,create_date,master_id)
                                  values(seq,rec_account.id,'期初',begin_amount_t,uid_t,period_t,uid_t,(now() at time zone 'UTC'),master_id_t);

                                  --2.查询期间内
                                 seq:=seq+1;
                                 for rec in select a.account_id,d.sub_account_id,b.name,b.date,a.sequence as line_sequence,a.name as line_name,
                                 sum(coalesce(lc_debit,0)) as debit,
                                  sum(coalesce(lc_credit,0)) as credit,c.dc_type
                                      from account_voucher_line a left join account_voucher b on a.master_id=b.id
                                                      left join cncw_account c on a.account_id=c.id
                                                      left join sub_account_line d on a.id=d.voucher_line_id
                                     where b.state='audited'
                                       and b.date between start_date_t and end_date_t
                                       and a.account_id=rec_account.id
                                     group by a.account_id,d.sub_account_id,b.name,b.date,c.dc_type,a.sequence,a.name
                                     order by b.date,b.name,d.sub_account_id
                                 loop
                                   end_t:=coalesce(begin_amount_t,0)+(coalesce(rec.debit,0)-coalesce(rec.credit,0))*(case when rec.dc_type='D' then 1 else -1 end);

                                   insert into account_summary(sequence,account_id,sub_account_id,name,date,line_name,begin_amount,debit,credit,end_amount,uid,period_id,create_uid,create_date,master_id)
                                   values(seq,rec.account_id,rec.sub_account_id,rec.name,rec.date,rec.line_name,begin_amount_t,rec.debit,rec.credit,end_t,uid_t,period_t,uid_t,(now() at time zone 'UTC'),master_id_t);

                                   seq:=seq+1;
                                   begin_amount_t:=end_t;
                                 end loop;
                             end loop;
                         end;
                         $BODY$
                          LANGUAGE plpgsql VOLATILE
                          COST 100;
                   """)
